Database systems typically store database objects (e.g. tables, indexes, etc.) on disk, and load data items from those database objects into volatile memory on an as-needed basis. Once loaded into volatile memory, the data items may remain cached in volatile memory so that subsequent accesses to the same data items will not incur the overhead of accessing a disk. Those data items may be replaced in cache, for example, to make room in volatile memory to store other data items that have been requested.
A certain type of query is a query with a clause that requires multiple iterations to execute, such as a query with a recursive clause. In a query with a clause that requires multiple iterations to execute, the results from a previous iteration are used to execute the next iteration. In addition, some data inputs are repeatedly used such that they don't change from iteration to iteration. To execute this type of query, a plan is generated regarding the order in which to apply each database operation of the plurality of database operations required by the iterative clause. A cursor stores this plan, and then the plan is executed multiple times—once for each iteration.
Unfortunately, because the end results of a previous iteration are always different, every database operation must be applied for every single iteration. Nothing is cached from the previous iterations except perhaps the end results of the immediately preceding iteration. The computational expense of performing many database operations over and over can monopolize the system resources of a database management system (DBMS).
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.